# -*- coding: utf-8 -*-#
#-------------------------------------------------------------------------------
# 建立者:        博智科技  
# Name:         demo1442
# Description:
# Author:       yzl
# Date:         2019-02-10
#-------------------------------------------------------------------------------


import sqlite3
import os
import  json

dbpath = 'db/mydb.sqlite'

if not os.path.exists(dbpath):
    # 1 创建连接
    conn = sqlite3.connect(dbpath)
    # 2 创建游标
    c = conn.cursor() #游标
    sql = """
        create table persons(
            id int primary key not null,
            name text not null,
            age int not null,
            address varchar(100),
            salary real
        );
    """
    # 3 执行sql语句
    c.execute(sql)
    # 4 提交
    conn.commit()
    # 5 关闭数据连接
    conn.close()
    print('数据库创建成功')

# 创建连接
conn = sqlite3.connect(dbpath)
# 创建游标
c = conn.cursor()
# sql = """
#     insert into persons(id,name,age,address,salary)
#     values(2,'张三1',30,'株洲',14000)
# """
# 执行sql语句
# c.execute(sql)
# # 提交
# conn.commit()
# # 关闭数据库连接
# conn.close()
# print('写入成功')

# sql = """
#     insert into persons(id,name,age,address,salary)
#     values(2,'李四',32,'株洲',22000)
# """
# c.execute(sql)
#
# sql = """
#     insert into persons(id,name,age,address,salary)
#     values(3,'李四',32,'湘潭',18000)
# """
# c.execute(sql)
#
# sql = """
#     insert into persons(id,name,age,address,salary)
#     values(4,'王五',32,'岳阳',15000)
# """
# c.execute(sql)
#
# conn.commit()
# print('写入数据成功')

sql = """
    select * from persons order by id
"""
persons = c.execute(sql)
result = []

for p in persons:
    value = {}
    value['id'] = p[0]
    value['name'] = p[1]
    value['age'] = p[2]
    value['address'] = p[3]
    value['salary'] = p[4]
    result.append(value)

conn.close()

print(result)
# 转换 成 json字符串
jsonstr = json.dumps(result)
print(jsonstr)